ExecuteNonQuery is a method from the SQlCommand Class in the System.Data.SqlClient namespace. It executes a T-SQL query and returns the number of rows affected. Below are examples of how to run an Insert, Delete, and Update statements using the ExecuteNonQuery method in both C# and VB.NET.
Required namespaces
C#
using System.Data; using System.Data.SqlClient;
VB.NET
Imports System.Data Imports System.Data.SqlClient
Insert
The insert function below will return the integer 1, as one row will be inserted to the database:
C#
protected int InsertEmployee() { string _name = "Mike"; string _departmenmt = "Engineering"; double _salary = 2000; string connetionString = "Data Source=TutorialsPanel-PC\SQLEXPRESS; Initial Catalog=TutorialsPanel;Integrated Security=True;"; using (SqlConnection conn = new SqlConnection(connetionString)) { using (SqlCommand cmd = new SqlCommand("INSERT INTO Employees (Name, Department, Salary) VALUES (@Name, @Department, @Salary)", conn)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@Name", _name); cmd.Parameters.AddWithValue("@Department", _departmenmt); cmd.Parameters.AddWithValue("@Salary", _salary); conn.Open(); int i = cmd.ExecuteNonQuery(); conn.Close(); return i; } } }
VB.NET
Protected Function InsertEmployee() As Integer Dim _name As String = "Mike" Dim _departmenmt As String = "Engineering" Dim _salary As Double = 2000 Dim connetionString As String = "Data Source=TutorialsPanel-PC\SQLEXPRESS; Initial Catalog=TutorialsPanel;Integrated Security=True;" Using conn As New SqlConnection(connetionString) Using cmd As New SqlCommand("INSERT INTO Employees (Name, Department, Salary) VALUES (@Name, @Department, @Salary)", conn) cmd.CommandType = CommandType.Text cmd.Parameters.AddWithValue("@Name", _name) cmd.Parameters.AddWithValue("@City", _departmenmt) cmd.Parameters.AddWithValue("@City", _salary) conn.Open() Dim i As Integer = cmd.ExecuteNonQuery() conn.Close() Return i End Using End Using End Function
Delete
The delete function below will return the affected row, which is 1.
C#
protected int DeleteEmployee() { string _name = "Mike"; string connetionString = "Data Source=TutorialsPanel-PC\SQLEXPRESS; Initial Catalog=TutorialsPanel;Integrated Security=True;"; using (SqlConnection conn = new SqlConnection(connetionString)) { using (SqlCommand cmd = new SqlCommand("DELETE FROM Persons WHERE Name = @Name", conn)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@Name", _name); conn.Open(); int i = cmd.ExecuteNonQuery(); conn.Close(); return i; } } }
VB.NET
Protected Function DeleteEmployee() As Integer Dim _name As String = "Mike" Dim connetionString As String = "Data Source=TutorialsPanel-PC\SQLEXPRESS; Initial Catalog=TutorialsPanel;Integrated Security=True;" Using conn As New SqlConnection(connetionString) Using cmd As New SqlCommand("DELETE FROM Employees WHERE Name = @Name", conn) cmd.CommandType = CommandType.Text cmd.Parameters.AddWithValue("@Name", _name) conn.Open() Dim i As Integer = cmd.ExecuteNonQuery() conn.Close() Return i End Using End Using End Function
Update
C#
protected int UpdateEmployee() { string _name = "Mike"; string _departmenmt = "IT"; double _salary = 2500; string connetionString = "Data Source=TutorialsPanel-PC\SQLEXPRESS; Initial Catalog=TutorialsPanel;Integrated Security=True;"; using (SqlConnection conn = new SqlConnection(connetionString)) { using (SqlCommand cmd = new SqlCommand("UPDATE Employees SET Department = @Department, Salary = @Salary WHERE Name = @Name", conn)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@Name", _name); cmd.Parameters.AddWithValue("@Department", _departmenmt); cmd.Parameters.AddWithValue("@Salary", _salary); conn.Open(); int i = cmd.ExecuteNonQuery(); conn.Close(); return i; } } }
VB.NET
Protected Function UpdateEmployee() As Integer Dim _name As String = "Mike" Dim _departmenmt As String = "IT" Dim _salary As Double = 2500 Dim connetionString As String = "Data Source=TutorialsPanel-PC\SQLEXPRESS; Initial Catalog=TutorialsPanel;Integrated Security=True;" Using conn As New SqlConnection(connetionString) Using cmd As New SqlCommand("UPDATE Employees SET Department = @Department, Salary = @Salary WHERE Name = @Name", conn) cmd.CommandType = CommandType.Text cmd.Parameters.AddWithValue("@Name", _name) cmd.Parameters.AddWithValue("@Department", _departmenmt) cmd.Parameters.AddWithValue("@Salary", _salary) conn.Open() Dim i As Integer = cmd.ExecuteNonQuery() conn.Close() Return i End Using End Using End Function
Comments